﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using bzxxxt.admin.ashx;
using bzxxxt.admin.service;
using bzxxxt.dal;
using bzxxxt.model;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;

namespace bzxxxt.bll
{
    public class DeadBLL : BaseBLL
    {
        admin.entity.UserEntity user = admin.service.UserService.GetUser(HttpContext.Current);   //获取cookie里的用户对象

        public string searchDead(int graveId)
        {
            var list = from d in DB.tbDead
                       where d.graveid == graveId
                       select d;
            var data = from d in list
                       select new DeadModel
                           {
                               id = d.id,
                               name = d.name,
                               cardno = d.cardno,
                               code = d.code,
                               gender = d.gender,
                               birthday = d.birthday,
                               deathtime = d.deathtime,
                               nationId = d.nationid,
                               nationName = d.nationid,
                               areacode = d.areacode,
                               areaName = d.tbArea == null ? "" : d.tbArea.name,
                               profession = d.professionid,
                               education = d.educationid,
                               deathcause = d.educationid,
                               deathplace = d.deathplace,
                               birAddress = d.birthAddress,
                               homeAddress = d.homeAddress,
                               photo = d.photo == null ? "/admin/images/dead.jpg" : d.photo,
                               estArriveplace = d.estArriveplace,
                               estArrivetime = d.estArrivetime,
                               actFiretime = d.actFiretime,
                               estStoretime = d.estStoretime,
                               estStoreplace = d.estStoreplace,
                               relativeId = d.relativeid,
                               relationId = d.relationid,
                               relativeName = d.tbRelative == null ? "" : d.tbRelative.name,
                               relativeTel = d.tbRelative == null ? "" : d.tbRelative.tel,
                               storeTime = d.starttime,
                               storeType = d.storetype,
                               graveId = d.graveid,
                               description = d.description

                           };
            string json = JsonUtil.ToJson(data);

            return json;
        }

        public tbDead getDead(int deadId)
        {
            var d = DB.tbDead.FirstOrDefault(m => m.id == deadId);
            return d;
        }

        public string getParadiseName(string code) {
            var p = DB.tbParadise.FirstOrDefault(m => m.code == code);
            if (p == null) return "";
            else return p.name;
        }

        public string savedead(tbDead d)
        {
            DB.SaveChanges();
            return JsonUtil.ToJson(new { success = true, msg = "操作成功！" });
        }
        public List<DeadInfo> queryDeadList(string ids)
        {
            List<DeadInfo> list = new List<DeadInfo>();
            if (string.IsNullOrEmpty(ids))
            {
                return list;
            }
            List<int> dids = new List<int>();
            string[] d = ids.Split(',');
            foreach (string id in d)
            {
                dids.Add(int.Parse(id));
            }


            list = DB.tbDead.Where(m => dids.Contains(m.id)).Select(m => new DeadInfo()
            {
                deadname = m.name,
                deadcode = m.code,
                relativename = m.tbRelative == null ? "" : m.tbRelative.name,
                relativetel = m.tbRelative == null ? "" : m.tbRelative.tel,
                deadid = m.id
            }).ToList();
            return list;
        }
        /// <summary>
        /// 二维码查询死者信息
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public DeadModel queryDeadInfo(string code)
        {
            var d = DB.tbDead.FirstOrDefault(m => m.code == code);
            if (d == null)
            {
                return null;
            }
            var dead = new DeadModel
            {
                id = d.id,
                name = d.name,
                cardno = d.cardno,
                code = d.code,
                gender = d.gender,
                birthday = d.birthday,
                deathtime = d.deathtime,
                nationId = d.nationid,
                nationName = d.nationid,
                areacode = d.areacode,
                areaName = d.tbArea == null ? "" : d.tbArea.name,
                profession = d.professionid,
                education = d.educationid,
                deathcause = d.educationid,
                deathplace = d.deathplace,
                birAddress = d.birthAddress,
                homeAddress = d.homeAddress,
                photo = d.photo == null ? "/admin/images/dead.jpg" : d.photo,
                estArriveplace = d.estArriveplace,
                estArrivetime = d.estArrivetime,
                actFiretime = d.actFiretime,
                estStoretime = d.estStoretime,
                estStoreplace = d.estStoreplace,
                relativeId = d.relativeid,
                relationId = d.relationid,
                relativeName = d.tbRelative == null ? "" : d.tbRelative.name,
                relativeTel = d.tbRelative == null ? "" : d.tbRelative.tel,
                graveName = d.tbGrave == null ? "" : d.tbGrave.tbParadise.name + d.tbGrave.name,
                storeTime = d.starttime
            };
            return dead;
        }
        public string getDeadInfo(string code)
        {
            var d = DB.tbDead.FirstOrDefault(m => m.code == code);
            if (d == null)
            {

                return string.Format("<h1>编码为【{0}】的死者不存在！</h1>", code);
            }
            var dead = new DeadModel
                        {
                            id = d.id,
                            name = d.name,
                            cardno = d.cardno,
                            code = d.code,
                            gender = d.gender,
                            birthday = d.birthday,
                            deathtime = d.deathtime,
                            nationId = d.nationid,
                            nationName = d.nationid,
                            areacode = d.areacode,
                            areaName = d.tbArea == null ? "" : d.tbArea.name,
                            profession = d.professionid,
                            education = d.educationid,
                            deathcause = d.educationid,
                            deathplace = d.deathplace,
                            birAddress = d.birthAddress,
                            homeAddress = d.homeAddress,
                            photo = d.photo == null ? "/admin/images/dead.jpg" : d.photo,
                            estArriveplace = d.estArriveplace,
                            estArrivetime = d.estArrivetime,
                            actFiretime = d.actFiretime,
                            estStoretime = d.estStoretime,
                            estStoreplace = d.estStoreplace,
                            relativeId = d.relativeid,
                            relationId = d.relationid,
                            relativeName = d.tbRelative == null ? "" : d.tbRelative.name,
                            relativeTel = d.tbRelative == null ? "" : d.tbRelative.tel,
                            graveName = d.tbGrave == null ? "" : d.tbGrave.tbParadise.name + d.tbGrave.name,
                            storeTime = d.starttime
                        };
            string msg = "<link href='css/table.css' rel='stylesheet' type='text/css' />"
                + "<center><table class='tableForm'><tr><td colspan='2'>死者信息</td></tr>"
                + "<tr><td colspan='2'><img src='" + dead.photo + "'/></td></tr>"
                + "<tr><th>死者姓名</th><td>" + dead.name + "</td></tr>"
                + "<tr><th>死者性别</th><td>" + dead.gender + "</td></tr>"
                + "<tr><th>证件号</th><td>" + dead.cardno + "</td></tr>"
                + "<tr><th>民族</th><td>" + dead.nationId + "</td></tr>"
                + "<tr><th>所属镇村</th><td>" + dead.areaName + "</td></tr>"
                + "<tr><th>存放地点</th><td>" + dead.graveName + "</td></tr>"
                + "<tr><th>存放时间</th><td>" + dead.storeTime + "</td></tr>"
                + "<tr><th>联系人姓名</th><td>" + dead.relativeName + "</td></tr>"
                + "<tr><th>联系电话</th><td>" + dead.relativeTel + "</td></tr>"

                + "</table></center>";
            return msg;
        }
        public string getDeadRelative(string name, String areaName)
        {
            var dlist = from d in DB.tbDead
                        where d.name.Contains(name) && d.tbArea.name.Contains(areaName)
                        select new DeadModel
                        {
                            id = d.id,
                            name = d.name,
                            cardno = d.cardno,
                            code = d.code,
                            gender = d.gender,
                            birthday = d.birthday,
                            deathtime = d.deathtime,
                            nationId = d.nationid,
                            nationName = d.nationid,
                            areacode = d.areacode,
                            areaName = d.tbArea == null ? "" : d.tbArea.name,
                            profession = d.professionid,
                            education = d.educationid,
                            deathcause = d.educationid,
                            deathplace = d.deathplace,
                            birAddress = d.birthAddress,
                            homeAddress = d.homeAddress,
                            photo = d.photo == null ? "/admin/images/dead.jpg" : d.photo,
                            estArriveplace = d.estArriveplace,
                            estArrivetime = d.estArrivetime,
                            actFiretime = d.actFiretime,
                            estStoretime = d.estStoretime,
                            estStoreplace = d.estStoreplace,
                            relativeId = d.relativeid,
                            relationId = d.relationid,
                            relativeName = d.tbRelative == null ? "" : d.tbRelative.name,
                            relativeTel = d.tbRelative == null ? "" : d.tbRelative.tel,
                            graveName = d.tbGrave == null ? "" : d.tbGrave.tbParadise.name + d.tbGrave.name,
                            storeTime = d.starttime
                        };
            return JsonUtil.ToJson(new { success = true, msg = dlist });
        }

        public string getDeadRelativeByName(string name)
        {
            var dlist = from d in DB.tbDead
                        where d.name.Contains(name)
                        select new DeadModel
                        {
                            id = d.id,
                            name = d.name,
                            cardno = d.cardno,
                            code = d.code,
                            gender = d.gender,
                            birthday = d.birthday,
                            deathtime = d.deathtime,
                            nationId = d.nationid,
                            nationName = d.nationid,
                            areacode = d.areacode,
                            areaName = d.tbArea == null ? "" : d.tbArea.name,
                            profession = d.professionid,
                            education = d.educationid,
                            deathcause = d.educationid,
                            deathplace = d.deathplace,
                            birAddress = d.birthAddress,
                            homeAddress = d.homeAddress,                            
                            estArriveplace = d.estArriveplace,
                            estArrivetime = d.estArrivetime,
                            actFiretime = d.actFiretime,
                            estStoretime = d.estStoretime,
                            estStoreplace = d.estStoreplace,
                            relativeId = d.relativeid,
                            relationId = d.relationid,
                            relativeName = d.tbRelative == null ? "" : d.tbRelative.name,
                            relativeTel = d.tbRelative == null ? "" : d.tbRelative.tel,
                            graveName = d.tbGrave == null ? "" : d.tbGrave.tbParadise.name + d.tbGrave.name,
                            storeTime = d.starttime
                        };
            return JsonUtil.ToJson(dlist);
        }

        public string getDeadRelative(string cardno)
        {
            var dlist = from d in DB.tbDead
                        where d.cardno == cardno
                        select new DeadModel
                        {
                            id = d.id,
                            name = d.name,
                            cardno = d.cardno,
                            code = d.code,
                            gender = d.gender,
                            birthday = d.birthday,
                            deathtime = d.deathtime,
                            nationId = d.nationid,
                            nationName = d.nationid,
                            areacode = d.areacode,
                            areaName = d.tbArea == null ? "" : d.tbArea.name,
                            profession = d.professionid,
                            education = d.educationid,
                            deathcause = d.educationid,
                            deathplace = d.deathplace,
                            birAddress = d.birthAddress,
                            homeAddress = d.homeAddress,
                            photo = d.photo == null ? "/admin/images/dead.jpg" : d.photo,
                            estArriveplace = d.estArriveplace,
                            estArrivetime = d.estArrivetime,
                            actFiretime = d.actFiretime,
                            estStoretime = d.estStoretime,
                            estStoreplace = d.estStoreplace,
                            relativeId = d.relativeid,
                            relationId = d.relationid,
                            relativeName = d.tbRelative == null ? "" : d.tbRelative.name,
                            relativeTel = d.tbRelative == null ? "" : d.tbRelative.tel,
                            graveName = d.tbGrave == null ? "" : d.tbGrave.tbParadise.name + d.tbGrave.name,
                            storeTime = d.starttime
                        };
            return JsonUtil.ToJson(new { success = true, msg = dlist.FirstOrDefault() });
        }


        public string GetListByPage(int pageSize, int pageIndex, string sort, string where = "1=1", string flag="1")
        {
            int totalCount = 0;
            if (user.roleNames.Contains("镇级管理员") || user.roleNames.Contains("骨灰堂管理员"))
            {
                string s = "";
                user.paradiseIds.ForEach(m => { s += m + ","; });
                s = s.Trim(',');
                if (flag != "1")
                {
                    var names = DB.tbParadise.Where(m => user.paradiseIds.Contains(m.id)).Select(m => m.name).ToList();
                    string ns = "";
                    names.ForEach(m => { ns += "'" + m.Trim() + "',"; });
                    ns = ns.Trim(',');
                    where = " estStoreplace in (" + ns + ") and " + where;
                }
                where = string.Format("(graveid in (select id from tbGrave where paradiseId in ({0}))) and ", s)  + where;
            }

            SqlParameter paramTableName = new SqlParameter("@tableName", "tbDead");
            SqlParameter paramColumns = new SqlParameter("@columns", "*");
            SqlParameter paramOrder = new SqlParameter("@order", sort);
            SqlParameter paramPageSize = new SqlParameter("@pageSize", pageSize);
            SqlParameter paramPageIndex = new SqlParameter("@pageIndex", pageIndex);
            SqlParameter paramWhere = new SqlParameter("@where", where);
            SqlParameter paramTotalCount = new SqlParameter("@totalCount", totalCount);
            paramTotalCount.Direction = ParameterDirection.Output;
            var p = DB.tbDead.SqlQuery("exec sp_Pager @tableName,@columns,@order,@pageSize,@pageIndex,@where,@totalCount output",
                new SqlParameter[] { paramTableName, paramColumns, paramOrder, paramPageSize, paramPageIndex, paramWhere, paramTotalCount }).ToList();
            totalCount = Convert.ToInt32(paramTotalCount.Value);

            var list = from d in p
                       select new DeadModel
                       {
                           id = d.id,
                           name = d.name,
                           cardno = d.cardno,
                           code = d.code,
                           gender = d.gender,
                           birthday = d.birthday,
                           deathtime = d.deathtime,
                           nationId = d.nationid,
                           nationName = d.nationid,
                           areacode = d.areacode,
                           areaName = d.tbArea == null ? "" : d.tbArea.name,
                           profession = d.professionid,
                           education = d.educationid,
                           deathcause = d.educationid,
                           deathplace = d.deathplace,
                           birAddress = d.birthAddress,
                           homeAddress = d.homeAddress,
                           photo = d.photo == null ? "/admin/images/dead.jpg" : d.photo,
                           estArriveplace = d.estArriveplace,
                           estArrivetime = d.estArrivetime,
                           actFiretime = d.actFiretime,
                           estStoretime = d.estStoretime,
                           estStoreplace = d.estStoreplace,
                           relativeId = d.relativeid,
                           relationId = d.relationid,
                           relativeName = d.tbRelative == null ? "" : d.tbRelative.name,
                           relativeTel = d.tbRelative == null ? "" : d.tbRelative.tel,
                           graveName = d.tbGrave == null ? "" : "[" + d.tbGrave.tbParadise.name + "]" + d.tbGrave.name,
                           storeTime = d.starttime
                       };
            var l = list.ToList();            
            string json = JsonUtil.ToJson(new { total = totalCount, rows = l });
            return json;
        }
        public string AddDeadRelative(tbDead dead)
        {

            //int relativeId = dead.tbRelative.id;
            //if (relativeId < 1)
            //{
            //    DB.tbRelative.Add(dead.tbRelative); 
            //}
            //else
            //{
            //    var r = DB.tbRelative.FirstOrDefault(m => m.id == relativeId);
            //    var relative = dead.tbRelative;
            //    r.name = relative.name;
            //    r.cardno = relative.cardno;
            //    r.addr = relative.addr;
            //    r.areacode = relative.areacode;
            //    r.company = relative.company;               
            //    r.gender = relative.gender;
            //    r.tel = relative.tel;
            //}
            DB.tbDead.Add(dead);
            DB.SaveChanges();
            return JsonUtil.ToJson(new { msg = "添加成功!", success = true });
        }
        public string AddDead(tbDead dead)
        {
            var d = DB.tbDead.Count(m => (!string.IsNullOrEmpty(m.cardno) && m.cardno == dead.cardno) || (m.name == dead.name && m.areacode == dead.areacode && m.homeAddress == dead.homeAddress));
            if (d > 0)
            {
                return JsonUtil.ToJson(new { msg = "证件号已经存在，添加失败!", success = false });
            }
            DB.tbDead.Add(dead);
            DB.SaveChanges();
            return JsonUtil.ToJson(new { msg = "添加成功!", success = true });
        }


        public string EditDeadRelatvie(tbDead dead)
        {
            var relative = dead.tbRelative;
            var d = DB.tbDead.FirstOrDefault(m => m.id == dead.id);
            var r = DB.tbRelative.Where(m => m.id == relative.id).FirstOrDefault();
            if (d != null)
            {
                d.relativeid = r.id;
                d.relationid = dead.relationid;
                d.name = dead.name;
                d.cardno = dead.cardno;
                d.actFiretime = dead.actFiretime;
                d.birthAddress = dead.birthAddress;
                d.areacode = dead.areacode;
                d.deathtime = dead.deathtime;
                d.gender = dead.gender;
                d.homeAddress = dead.homeAddress;
                d.estArriveplace = dead.estArriveplace;
                d.estArrivetime = dead.estArrivetime;
                d.addtime = DateTime.Now;
                d.nationid = dead.nationid;
                d.photo = dead.photo;
            }
            DB.SaveChanges();
            return JsonUtil.ToJson(new { msg = "修改成功!", success = true });
        }


        public string EditDead(tbDead dead)
        {
            var d = DB.tbDead.FirstOrDefault(m => m.id == dead.id);
            var dd = DB.tbDead.FirstOrDefault(m => (!string.IsNullOrEmpty(m.cardno) && m.cardno == dead.cardno));
            if (d != null)
            {
                if (dd != null && dd.id != d.id)
                {
                    return JsonUtil.ToJson(new { msg = "证件号已经存在，修改失败!", success = false });
                }
                d.name = dead.name;
                d.gender = dead.gender;
                d.nationid = dead.nationid;
                d.areacode = dead.areacode;
                d.cardtype = dead.cardtype;
                d.cardno = dead.cardno;
                d.professionid = dead.professionid;
                d.educationid = dead.educationid;
                d.deathcaurseid = dead.deathcaurseid;
                d.deathplace = dead.deathplace;
                d.actFiretime = dead.actFiretime;
                d.deathtime = dead.deathtime;
                d.homeAddress = dead.homeAddress;
                d.birthAddress = dead.birthAddress;

                //d.estArriveplace = dead.estArriveplace;
                //d.estArrivetime = dead.estArrivetime;
                //d.relationname = dead.relationname;
                //d.relationid = dead.relationid;
                d.photo = dead.photo;
                d.updatetime = DateTime.Now;
            }
            DB.SaveChanges();
            return JsonUtil.ToJson(new { msg = "修改成功!", success = true });
        }

        public string DelDead(string deadIds)
        {
            string[] ids = deadIds.Split(',');
            List<int> lstIds = new List<int>();
            foreach (var s in ids)
            {
                lstIds.Add(int.Parse(s));
            }
            var list = DB.tbDead.Where(m => lstIds.Contains(m.id)).ToList();
            string msg = "删除死者:";
            list.ForEach(m =>
            {
                m.tbMoveHis.Clear();
                m.tbEcoBural.Clear();
                m.tbBusiness.Clear();
                if (m.tbGrave != null)
                {
                    m.tbGrave.qty--;
                    if (m.tbGrave.qty < 0) m.tbGrave.qty = 0;
                }
                DB.tbDead.Remove(m);
                msg += m.name + "[" + m.id + "],";
            });
            msg = msg.Trim(',');
            DB.SaveChanges();
            tbOperateLog log = new tbOperateLog();
            log.operateDate = DateTime.Now;
            log.userIp = HttpContext.Current.Request.UserHostAddress;
            log.action = "delete";
            log.success = true;
            log.userName = user.userName;
            log.description = msg;
            DB.tbOperateLog.Add(log);
            DB.SaveChanges();
            return JsonUtil.ToJson(new { msg = "删除成功!", success = true });
        }

        private string getCellVaue(ICell cell)
        {
            string v = "";
            switch (cell.CellType)
            {
                case CellType.String:
                    v = cell.StringCellValue;

                    break;
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        DateTime dt = DateTime.FromOADate(cell.NumericCellValue);
                        v = dt.ToString("yyyy-MM-dd");
                    }
                    else
                    {
                        v = Convert.ToString(cell.NumericCellValue);
                    }
                    break;
                case CellType.Boolean:
                    v = Convert.ToString(cell.BooleanCellValue);
                    break;
                case CellType.Error:
                    v = ErrorEval.GetText(cell.ErrorCellValue);
                    break;
                default:
                    v = "";
                    break;
            }
            return v.Trim();
        }

        /// <summary>
        /// 读取excel,导入死者数据
        /// </summary>
        /// <param name="strFileName">excel文件路径</param>
        /// <param name="sheet">需要导出的sheet序号</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <returns></returns>
        public void ParseDeadFile(string strFileName, int SheetIndex, int HeaderRowIndex)
        {
            List<ImportModel> list = new List<ImportModel>();
            IWorkbook wb;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                wb = WorkbookFactory.Create(file);
            }
            ISheet sheet = wb.GetSheetAt(SheetIndex);
            //table = ImportDt(sheet, HeaderRowIndex, needHeader);

            DataTable table = new DataTable();
            IRow headerRow;
            int cellCount;
            StringBuilder sbMsg = new StringBuilder();
            try
            {
                //检查表格的标题是否符合模板格式
                

                headerRow = sheet.GetRow(HeaderRowIndex);
                cellCount = headerRow.LastCellNum;
                string[] headers = { "亡者姓名", "亡者性别", "亡者身份证", "与联系人关系", "火化时间", "联系人姓名", "联系人性别", "联系电话", "村居委", "所在镇", "详细住址", "身份证号", "预计存放时间", "预计存放地点" };
                if (cellCount != headers.Length)
                {
                    throw new Exception("数据文件格式列数与模板列数不符！");
                }
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    string headtitle = headerRow.GetCell(i).ToString();
                    if (headers[i] != headtitle)
                    {
                        throw new Exception("数据文件第" + i + "列名称与数据模板不符！");
                    }
                }
                int rowCount = sheet.LastRowNum;

                StringBuilder sbLine = new StringBuilder();
                for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)
                {
                    ImportModel m = new ImportModel();
                    sbLine.Clear();
                    try
                    {
                        IRow row = sheet.GetRow(i);
                        m.id = i + 1;
                        if (row == null)
                        {
                            sbMsg.AppendFormat("第{0}行无数据!<br/>", i + 1);
                            continue;
                        }

                        string deadname = getCellVaue(row.GetCell(0));
                        m.dname = deadname;


                        if (string.IsNullOrEmpty(deadname))
                        {
                            sbLine.AppendFormat("第{0}行死者姓名不能为空!<br/>", i + 1);
                        }

                        string dsex = getCellVaue(row.GetCell(1));
                        m.dsex = dsex;
                        if (string.IsNullOrEmpty(m.dsex)) m.dsex = "不详";

                        m.dcardno = getCellVaue(row.GetCell(2));

                        m.relation = getCellVaue(row.GetCell(3));
                        string firtimestr = getCellVaue(row.GetCell(4));
                        try
                        {                                                        
                            m.fireTime = DateTime.Parse(firtimestr); ;
                        }
                        catch {
                            sbLine.AppendFormat("第{0}行死者火化时间格式【{1}】不正确! ", i + 1, firtimestr);
                        }

                        m.rname = getCellVaue(row.GetCell(5));
                        if (string.IsNullOrEmpty(m.rname))
                        {
                            sbLine.AppendFormat("第{0}行联系人姓名不能为空! ", i + 1);
                        }
                        m.rsex = getCellVaue(row.GetCell(6));
                        if (string.IsNullOrEmpty(m.rsex))
                        {
                            sbLine.AppendFormat("第{0}行联系人电话不能为空! ", i + 1);
                        }
                        m.rtel = getCellVaue(row.GetCell(7));

                        string cun = getCellVaue(row.GetCell(8));
                        //处理村名
                        var c = DB.tbArea.FirstOrDefault(area => area.name.IndexOf(cun) >= 0);
                        if (c == null)
                        {
                            sbLine.AppendFormat("第{0}行村别【{1}】无效! ", i + 1, cun);
                        }
                        else
                        {
                            m.cun = c.code;
                        }
                        string zheng = getCellVaue(row.GetCell(9));

                        string zu = getCellVaue(row.GetCell(10));
                        m.zu = zu;

                        m.rcardno = getCellVaue(row.GetCell(11));

                        string storetimestr = getCellVaue(row.GetCell(12));
                        try
                        {
                            m.storeTime =  DateTime.Parse(storetimestr);
                        }
                        catch {
                            sbLine.AppendFormat("第{0}行死者预存时间格式【{1}】不正确! ", i + 1, storetimestr);
                        }

                        m.pname = getCellVaue(row.GetCell(13));
                        var pp = DB.tbParadise.FirstOrDefault(p => p.name.Contains(m.pname));
                        if (pp == null) {
                            sbLine.AppendFormat("第{0}行预存骨灰堂【{1}】不存在! ", i + 1, m.pname);
                        }

                    }
                    catch (Exception ex1)
                    {
                        sbLine.AppendFormat(" 第{0}行数据格式错误!" + ex1.Message, i + 1);
                    }

                    if (sbLine.Length > 0)
                    {
                        sbMsg.AppendLine(sbLine.ToString() + "<br/>");
                        sbLine.Clear();
                    }
                    else
                    {
                        list.Add(m);
                    }
                } //end for

                if (sbMsg.Length > 0)
                {
                    throw new Exception(sbMsg.ToString());
                }
            }
            catch (Exception ex)
            {
                //string path = HttpContext.Current.Server.MapPath("/admin/upload/log.html");
                //FileStream fs = new FileStream(path, FileMode.Create);
                //StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
                //sw.WriteLine("解析数据失败:<br/>");
                //sw.WriteLine(ex.Message);
                //sw.Close();
                //throw new Exception("解析数据失败！");
                throw ex;
            }

            sheet = null;

            foreach (ImportModel m in list)
            {
                try
                {
                    tbRelative relative = DB.tbRelative.FirstOrDefault(r => r.name == m.rname && r.areacode == m.cun);
                    if (!string.IsNullOrEmpty(m.rcardno)) {
                        relative = DB.tbRelative.FirstOrDefault(r => r.name==m.rname && r.cardno == m.rcardno);
                    }
                    else if (!string.IsNullOrEmpty(m.rtel)) {
                        relative = DB.tbRelative.FirstOrDefault(r => r.name == m.rname && r.tel == m.rtel);
                    }
                    else if (!string.IsNullOrEmpty(m.zu))
                    {
                        relative = DB.tbRelative.FirstOrDefault(r => r.name == m.rname && r.areacode == m.cun && r.addr == m.zu);
                    }
                    if (relative == null)
                    {
                        relative = new tbRelative();
                        relative.name = m.rname;
                        relative.gender = m.rsex;
                        relative.tel = m.rtel;
                        relative.areacode = m.cun;
                        relative.cardno = m.rcardno;
                        relative.addr = m.zu;
                        DB.tbRelative.Add(relative);
                    }
                    tbDead dead = null;
                    if (!string.IsNullOrEmpty(m.dcardno)) {
                        dead = DB.tbDead.FirstOrDefault(d => d.name == m.dname && d.cardno == m.dcardno);
                    }else if (!string.IsNullOrEmpty(m.zu)){
                        dead = DB.tbDead.FirstOrDefault(d => d.name == m.dname && d.areacode == m.cun && d.homeAddress == m.zu);
                    }
                    //var dead = DB.tbDead.FirstOrDefault(d => (m.dcardno != "" && d.cardno == m.dcardno) || (d.name == m.dname && d.tbRelative.name == m.rname && d.tbRelative.tel == m.rtel));
                    if (dead == null)
                    {
                        dead = new tbDead();
                        dead.name = m.dname;
                        dead.gender = m.dsex;
                        dead.cardno = m.dcardno;
                        dead.homeAddress = m.zu;
                        dead.areacode = m.cun;
                        dead.actFiretime = m.fireTime;
                        dead.estStoretime = m.storeTime;
                        dead.estStoreplace = m.pname;
                        dead.relationid = m.relation;
                        DB.tbDead.Add(dead);
                        if (dead.tbRelative == null)
                        {
                            dead.tbRelative = relative;
                        }
                        DB.SaveChanges();
                        //sbMsg.AppendFormat("第{0}行死者【{1}】导入成功！<br/>", m.id, m.dname);
                    }
                    else
                    {
                        //sbMsg.AppendFormat("第{0}行死者【{1}】已存在！<br/>", m.id, m.dname);
                    }
                    
                }
                catch (Exception ex)
                {
                    //string path = HttpContext.Current.Server.MapPath("/admin/upload/log.html");
                    //FileStream fs = new FileStream(path, FileMode.Create);
                    //StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
                    //if(sbMsg.Length>0)  sw.WriteLine(sbMsg.ToString());
                    //sw.WriteLine("<br/>导入数据失败:<br/>");
                    //sw.WriteLine(ex.Message);
                    //sw.Close();
                    //throw new Exception("导入数据失败！");
                    throw new Exception(sbMsg.ToString()+" "+ex.Message);
                }
            }
            if (sbMsg.Length > 0)
            {
                //string path = HttpContext.Current.Server.MapPath("/admin/upload/log.html");
                //FileStream fs = new FileStream(path, FileMode.Create);
                //StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
                //if (sbMsg.Length > 0) sw.WriteLine(sbMsg.ToString());
                //sw.WriteLine("<br/>导入数据完成！<br/>");                
                //sw.Close();    
                throw new Exception(sbMsg.ToString());
            }
        }
    }
}